Date formatting problems

[JTRTech]

I am trying to pull the work period start date for a script.
Am using;

SELECT TOP 1 [StartDate]
FROM [WorkPeriods]
ORDER BY [StartDate] DESC

and

	var wpStartDateQry		= "@@WPStartDate";																						
	var wpstartDateRaw 		= sql.Query(wpStartDateQry).First;

returns;

24/12/2015 12:23:16

But the value in the database is

2015-12-24 12:23:16.973

The format returned by the query is giving me great difficulty as is in wrong format to put into SQL query later in the script.
.getDate() etc doesnt work on it

[JTRTech]

Ok, system time solved it but script doesn’t seem to like the time part now!! If manually put yyyy-MM-dd on its own it works but with time I get an error!

[QMcKay]

If you look at some of my scripts, the way to force the date format is to do a conversion using a pre-defined value for formatting.

Take my word for it - the following is what you want:

convert(varchar(25),[StartDate],126)

Believe it or not, the important part is the number 126. This is a pre-defined integer representing this format:

yyyy-MM-ddTHH:mm:ss.ttt

Yes, there is a T in there separating the date from the time. Use SUBSTRING or LEFT methods to get the pieces you want:

left(convert(varchar(25),[StartDate],126),10) -- the date as yyyy-MM-dd
substring(convert(varchar(25),[StartDate],126),12,8) -- the time as HH:mm:ss

Your query:

SELECT TOP 1 convert(varchar(25),[StartDate],126)
FROM [WorkPeriods]
ORDER BY [StartDate] DESC

Reference: https://msdn.microsoft.com/en-us/library/ms187928.aspx

126 represents ISO8601 format.
121 does the same thing without the T in the middle.

But having the T can be useful when you use something like this in JS:

var dt = variable.split('T');
echo dt[0]; // print the Date
echo dt[1]; // print the Time
echo dt[0] + ' ' + dt[1]; // print Date/Time with space separating them

[JTRTech]

@QMcKay what does echo achive?
Googled it but struggling to follow the uses, I usually understand your explanations.

[Jesse]

Echo prints it on screen. If in command shell it will show that value.

[JTRTech]

Thanks kendash

Am still struggling to get it to work, cant just use date encase there is a past midnight sale from day before…
Workperiod ID would be nice in ticket table LOL

Get an exception error…
Tried @QMcKay format and split script
Get 2016-01-02 19:15:35.583
but with t.[Date] > '@3'
Where @3 is the parameter/value input and t. is the Tickets table, to the SQL script throws an error.

If i use '2016-01-02 19:15:35.583' I still get an error so something wrong somewhere…
Thats the format in the table…

[QMcKay]

Lots of things to try, but I can’t tell the answer unless you show the full query.

Run the query SSMS to find out where the error is being thrown.

Try stripping the .xxx portion.

declare the variable in your SQL and assign it the @3 value, then convert it to a proper format.

declare @dttvar datetime = convert(varchar(25),'@3',126)

...

WHERE [Date] > @dtvar

[JTRTech]

Thanks Q, have had a bit of a drawback so am having to restructure the scripts as negated to take order tags and gift/void order states into account ass order price is still recorded in full and reported seperatly

[JTRTech]

Its 100% having the colons in the time in a script which is passing parameters into a seperate SQL script.
This works;

This works for date without time

However with time (colons using @@ doesnt;