September 22, 2013 at 2:57 pm
Hi,
I'm trying to insert random date in yyyy-mm format to have expiry Credit card date column.
Haw can I do it? is it possible at all?
I know that I can sore the full (yyyy-mm-dd) and then to select with: convert(char(7),column_a,121)
but this is not the way I'm looking for .
Thanks
September 22, 2013 at 3:35 pm
This might be what you are looking for:
SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]
Result:
2013/09
]
September 23, 2013 at 12:43 pm
If I want to select it's working but as I mentioned I'm trying to insert and because my column is a 'date type' when I'm using CONVERT(VARCHAR(7), GETDATE(), 111)-- AS [YYYY/MM]
I'm getting this error message:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Do u have any suggestions?
Thanks for trying to help
September 23, 2013 at 1:31 pm
In order to insert data into a date data type - you need a valid date or string that can be converted into a date.
If you are looking for random dates - then wouldn't you want random days also? Or are you looking for random months only? If random months only, then just pick a day - either the 1st or 15th would be my recommendation.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 23, 2013 at 1:47 pm
I don't have a problem to get a random month or a day I just using this :
UPDATE table_a
SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')
But the column that I'm trying to populate is a 'credit card expiry date' and it need to be '2015/01' (yyyy/mm).
September 23, 2013 at 2:06 pm
golansimani (9/23/2013)
I don't have a problem to get a random month or a day I just using this :UPDATE table_a
SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')
But the column that I'm trying to populate is a 'credit card expiry date' and it need to be '2015/01' (yyyy/mm).
Since your target column is not a datetime you can't use the results of DATEADD. That function returns a datetime. You would have to kludge this a bit more.
SET column_a = cast(year(DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')) as char(4))
+ '/'
+ right('0' + cast(month(DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')) as varchar(2)), 2)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2013 at 3:03 pm
Its not working , I got this message :
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
September 23, 2013 at 3:10 pm
golansimani (9/23/2013)
Its not working , I got this message :Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
What is the datatype of column_a? I suspect that column is actually a datetime based on your message.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2013 at 3:16 pm
Its a Date Data Type
September 23, 2013 at 3:19 pm
golansimani (9/23/2013)
Its a Date Data Type
Then the code you posted should work fine.
SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')
Remember there is not such thing as a format for date columns.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply