-1

I can't get an "Italian" datetime. What I am doing wrong? Here is my code:

DECLARE @Dat Nvarchar(100) set @Dat = '2014.05.25.;'
Declare @k int, @dat1 datetime, @dat2 datetime
set @k=0
set @k=CHARINDEX(';',@Dat)
set @dat2 = substring(@Dat, 1, @k-2)
set @dat1 = convert(datetime, @dat2, 105)
print @dat1

This prints:

mai 25 2014 12:00AM

However, I need "25-05-2014" as datetime so I can pass it to a procedure with a datetime variable.

Jeroen
  • 60,696
  • 40
  • 206
  • 339
user3484926
  • 47
  • 8
  • 1
    Your procedure takes a datetime parameter in a varchar? Time to rethink your architecture :) Oh, and when you're using `nvarchar` constants, make sure you use `N'Whatever'` instead of `'Whatever'`. – Luaan May 26 '14 at 08:23
  • 2
    Why are you trying to do this db-side? Formatting doesn't really belong on the db, it belongs in the display/reporting layer. If possible, return the full timestamp to the application and have your display code muck with it. – Clockwork-Muse May 26 '14 at 08:36

4 Answers4

1

If you want only to show the date, then you must try something like this:- 

SELECT CONVERT(VARCHAR, GETDATE(), 105)

The output will be:-

26-05-2014
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • However, I need "25-05-2014" as datetime so I can pass it to a procedure with a datetime variable. – user3484926 May 26 '14 at 08:40
  • @user3484926 A datetime variable stores a datetime value. '25-05-2014' is just the way you SEE that value! You can just pass that datetime to your usp directly. Why should you care about how that variable is displayed to you, if you just need it as parameter? – mordack550 May 26 '14 at 09:39
0

If you want to use an explicit date value without specifying a format, use the ISO format: yyyyMMdd --> "20140525" in your example. All other ways of writing can depend on the system regional settings.

If your procedure is created correctly, it'll have a DATETIME or DATETIME2 parameter and is NOT expecting a string with a specific date format. Example of parsing string as datetime with an explicit format: 

DECLARE @DATEVAR DATETIME
SET @DATEVAR = CONVERT(DATETIME, '25-05-2014', 105)

With ISO FORMAT: 

SET @DATEVAR = '20140525'

If you want to DISPLAY your date variable as a string in a certain format, you need to convert it the other way around:

DECLARE @DATEVAR DATETIME
DECLARE @STRINGVAR VARCHAR(50)
SET @DATEVAR = CONVERT(DATETIME, '25-05-2014', 105)
SET @STRINGVAR = CONVERT(VARCHAR(50), @DATEVAR, 105)
PRINT @STRINGVAR

In the example of your string, parse it like this and you'll simply have the ISO format left. Obviously, you could also use another date format and make it explicit.

SET @DATEVAR = REPLACE(REPLACE('2014.05.25.;', ';', ''), '.', '')
M.Stoop
  • 119
  • 2
0

As I said in one comment down there, if you need that variable as parameter, you should not care about how it's formatted or displayed. A datetime field is always "saved" as 2 integers, one for the time and one for the date.

Using the max date SQL Server can calculate (9999/12/31) we have:

DateInt  DateBinary
-------------------
2958463  0x002D247F

TimeInt  TimeBinary
-------------------
25919999 0x018B81FF

So if you need a DATETIME value just for passing to a USP, use the value you already have! There's no need to convert it to a string, that is just the graphical representation of the value stored in that variable.

mordack550
  • 492
  • 4
  • 14
  • Source: http://stackoverflow.com/questions/6656658/when-storing-a-datetime-in-sql-server-datetime-type-what-format-does-it-store – mordack550 May 26 '14 at 09:49
0

hello in the other hand you can change that conver.

and make your code look like what you will see.

from this: convert(datetime, @dat2, 105)

to this: convert(VARCHAR, @dat2, 5)

and this how i make the time in itlian you can try it, is working fine with me.

SET LANGUAGE Italian

Yuu
  • 21
  • 7